# -*- coding: utf-8 -*-
import re
import sqlite3
import requests
import time
from datetime import datetime, timedelta


def cleanhtml(html):
    pattern = '\s+|(&nbsp;)+'
    # pattern='\s+'
    html = re.sub(pattern, ' ', html)
    # 去掉html标记间的空格
    # pattern='> <'
    # html=re.sub(pattern,'><',html)
    return html.replace('> <', '><')

    
def getcookie():
    auth_url = 'http://127.0.0.1:50003/auth_npedi'
    auth = requests.get(auth_url, params={'site': 'npedi.new', 'action': 'remote'},
                        headers={'Accept': 'application/json'}).json()
    print auth
    l_cookie=auth['cookie'].split(';')
    if len(l_cookie)==2:
        cookies=dict(JSESSIONID=l_cookie[0],SESSION_ID_IN_BIZ=l_cookie[1])
    else:
        cookies=dict(JSESSIONID=l_cookie[0])
    # cookies=dict(JSESSIONID='XGtXQ3U72ldEpGxNzMdBC86H.standalone-node3')
    print cookies
    return cookies
    
def getdatabypage(pageno):
    querydatelist = gen_querydate(querydatecount)
    # http://www.npedi.com/ediportal-web/getEdiCusmov.action?matou=&vesselUn=&voyage=&ctnNo=&starttime1=2014-12-20%2010:25:08&endtime1=2014-12-30%2010:25:11&PageCount=5&gotoPage=2
    url = 'http://www.npedi.com/ediportal-web/getEdiCusmov.action?matou=&vesselUn=&voyage=&ctnNo=&starttime1=%s&endtime1=%s&PageCount=200&gotoPage=%s' % (
        querydatelist[-1], querydatelist[0], pageno)
    print url
    headers = { 'Accept-Language': 'zh-cn,zh;q=0.8,en-us;q=0.5,en;q=0.3'
              # , 'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8'
              # , 'DNT': '1'
              # , 'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:34.0) Gecko/20100101 Firefox/34.0'
              # , 'Accept-Encoding': 'gzip, deflate'
              }
    res = requests.get(url,headers=headers,cookies=getcookie())
    result = res.json()
    print result
    return result['totalPages'], result['result']
    
    
    
def getfetch():
    p=tp=1
    rl=list()
    while p<=tp:
        tp,r=getdatabypage(p)
        p=p+1
        rl.extend(r)
    print len(rl)
    
    # tds = cleanval(re.findall(
        # '<td height="24" nowrap="nowrap" class="dblue12">(.*?)</td>', cleanhtml(res.text)))
    # rows = []
    # for i in range(len(tds) / 13):
        # row = []
        # for j in tds[i * 13 + 1:i * 13 + 13]:
            # row.append(j)
        # rows.append(row)
    return rows


def cleanval(v):
    if type(v) == list or type(v) == tuple:
        return [None if x.strip() == '' else x.strip() for x in v]
    else:
        return None if v.strip() == '' else v.strip()


def gen_querydate(datecount):
    querydatelist = []
    queryday = datetime.today()
    for c in xrange(datecount):
        querydatelist.append(datetime.strftime(queryday - timedelta(days=c), '%Y-%m-%d %H:%M:%S'))
    return querydatelist

# ##########################################
# 主程序开始
# ##########################################

head = ('vessel', 'voyage', 'boxno', 'pier', 'boxsize', 'movtype', 'h986', 'destplace', 'instructtime', 'movflag', 'dealtime', 'feedback')
head = ( "aimPlace"             # 目的场站      编码
       , "companyCode"          # 码头/堆场     编码
       , "containerMoveType"    # 移箱类型      编码    
       , "controlStatus"        # 移箱标志      编码
       , "ctnNo"                # 集装箱号
       , "ctnSizeType"          # 箱型尺寸
       , "declarant"
       , "declarantTel"
       , "direct"
       , "freebackTime"         # 处理时间      YYYYmmDDHHMMSS
       , "id"
       , "isCheck986"           # H986
       , "moveReason"
       , "operatorTime"
       , "orderSendDate"        # 指令时间      YYYYmmDDHHMMSS
       , "originallyPlace"
       , "rremark"              # 反馈信息
       , "vesselEname"          # 船名
       , "vesselUn"             
       , "voyage"               # 航次
       )
querydatecount = 2

s = time.time()
rs = getfetch()
print 'fetch use second:', time.time() - s

s = time.time()
conn = sqlite3.connect('npedi.db3')
# conn.text_factory = str
# conn.row_factory = sqlite3.Row
querydatelist = gen_querydate(querydatecount)
sql = "delete from cusmov where instructtime>='%s 00:00:00' and instructtime<='%s 23:59:59'" % (
    querydatelist[-1], querydatelist[0])
conn.execute(sql)
sql = "delete from cusmov where instructtime<='%s 23:59:59'" % datetime.strftime(
    datetime.now() - timedelta(days=30), '%Y-%m-%d')
conn.execute(sql)
for r in rs:
    cols = ','.join(head)
    values = ','.join(['?'] * len(head))
    sql = 'insert into cusmov (%s) values(%s)' % (cols, values)
    conn.execute(sql, r)
conn.commit()
conn.close()
print 'write db use second:', time.time() - s
